﻿// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace SnapObjects.Data.Oracle
{
    internal class OracleProcedureParameterSchemaProvider : OracleRoutineParameterSchemaProvider
    {
        public OracleProcedureParameterSchemaProvider(DataContext dataContext) : base(dataContext)
        {
        }

        protected override bool IsParseProcedure => true;

        protected override ProcedureSchema CreateParameterSchema(
            string name,
            IReadOnlyList<AdoDbParameter> adoDbParameters,
            IReadOnlyList<DynamicModel> dbParameters)
        {
            return new ProcedureSchema(name, adoDbParameters);
        }

        protected override QueryParameter GetQueryParameter(string routineName, ISqlGenerationHelper helper)
        {
            var sqlText = this.GetSqlText(routineName, helper);

            var result = _dataContext.SqlExecutor.Select<DynamicModel>(sqlText);

            return new QueryParameter(USER_ARGUMENTS_TABLE, false, result);
        }

        protected override async Task<QueryParameter> GetQueryParameterAsync(
            string routineName, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var sqlText = this.GetSqlText(routineName, helper);

            var result = await _dataContext.SqlExecutor.SelectAsync<DynamicModel>(sqlText, new object[] { }, cancellationToken);

            return new QueryParameter(USER_ARGUMENTS_TABLE, false, result);
        }

        protected override QueryParameter GetQueryParameter(string owner, string routineName, ISqlGenerationHelper helper)
        {
            var sqlText = this.GetSqlText(owner, routineName, helper);

            var result = _dataContext.SqlExecutor.Select<DynamicModel>(sqlText);

            return new QueryParameter(ALL_ARGUMENTS_TABLE, false, result);
        }

        protected override async Task<QueryParameter> GetQueryParameterAsync(
            string owner, string routineName, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var sqlText = this.GetSqlText(owner, routineName, helper);

            var result = await _dataContext.SqlExecutor.SelectAsync<DynamicModel>(sqlText, new object[] { }, cancellationToken);

            return new QueryParameter(ALL_ARGUMENTS_TABLE, false, result);
        }

        protected override QueryParameter GetQueryParameter(string owner, string packageName, string routineName, ISqlGenerationHelper helper)
        {
            var sqlText = this.GetSqlText(owner, packageName, routineName, helper);

            var result = _dataContext.SqlExecutor.Select<DynamicModel>(sqlText);

            return new QueryParameter(ALL_ARGUMENTS_TABLE, true, result);
        }

        protected override async Task<QueryParameter> GetQueryParameterAsync(
            string owner, string packageName, string routineName, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var sqlText = this.GetSqlText(owner, packageName, routineName, helper);

            var result = await _dataContext.SqlExecutor.SelectAsync<DynamicModel>(sqlText, new object[] { }, cancellationToken);

            return new QueryParameter(ALL_ARGUMENTS_TABLE, true, result);
        }

        protected override QueryParameter GetQueryParameter(string packageName, string routineName, DynamicModel package, ISqlGenerationHelper helper)
        {
            var sqlText = this.GetSqlText(packageName, routineName, package, helper);

            var result = _dataContext.SqlExecutor.Select<DynamicModel>(sqlText.Item1);

            return new QueryParameter(sqlText.Item2, true, result);
        }

        protected override async Task<QueryParameter> GetQueryParameterAsync(
            string packageName, string routineName, DynamicModel package, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var sqlText = this.GetSqlText(packageName, routineName, package, helper);

            var result = await _dataContext.SqlExecutor.SelectAsync<DynamicModel>(sqlText.Item1, new object[] { }, cancellationToken);

            return new QueryParameter(sqlText.Item2, true, result);
        }

        private string GetSqlText(string routineName, ISqlGenerationHelper helper)
        {
            var procedureName = helper.RemoveDelimitIdentifier(routineName);

            var condition = " procs.object_type = 'PROCEDURE' " +
                $"and upper(procs.object_name) = upper('{procedureName}')";

            return String.Format(SQL_PROCEDURE, USER_PROCEDURES_TABLE, condition);
        }

        private string GetSqlText(string owner, string packageName, string routineName, ISqlGenerationHelper helper)
        {
            var condition = @" procs.object_type = 'PACKAGE' 
and upper(procs.owner) = upper('{0}') 
and upper(procs.object_name) = upper('{1}') 
and upper(procs.procedure_name) = upper('{2}')";

            var tmpOwner = helper.RemoveDelimitIdentifier(owner);
            var tmpPackageName = helper.RemoveDelimitIdentifier(packageName);
            var procedureName = helper.RemoveDelimitIdentifier(routineName);

            condition = String.Format(condition, tmpOwner, tmpPackageName, procedureName);

            return String.Format(SQL_PROCEDURE, ALL_PROCEDURES_TABLE, condition);
        }

        private string GetSqlText(string owner, string routineName, ISqlGenerationHelper helper)
        {
            var tmpOwner = helper.RemoveDelimitIdentifier(owner);
            var procedureName = helper.RemoveDelimitIdentifier(routineName);

            var condition = @" procs.object_type = 'PROCEDURE'  
and upper(procs.owner) = upper('{0}')  
and upper(procs.object_name) = upper('{1}') ";

            condition = String.Format(condition, tmpOwner, procedureName);

            return String.Format(SQL_PROCEDURE, ALL_PROCEDURES_TABLE, condition);
        }

        private (string, string) GetSqlText(string packageName, string routineName, DynamicModel package, ISqlGenerationHelper helper)
        {
            var procedureTable = ALL_PROCEDURES_TABLE;
            var argumentsTable = ALL_ARGUMENTS_TABLE;

            if (String.IsNullOrWhiteSpace(package.GetValue<string>(0)))
            {
                procedureTable = USER_PROCEDURES_TABLE;
                argumentsTable = USER_ARGUMENTS_TABLE;
            }

            var procedureName = helper.RemoveDelimitIdentifier(routineName);

            var tmpPackageName = helper.RemoveDelimitIdentifier(packageName);

            var condition = $" procs.object_type = 'PACKAGE' " +
                $"and upper(procs.object_name) = upper('{tmpPackageName}') " +
                $"and upper(procs.procedure_name) = upper('{procedureName}') ";

            var sqlText = String.Format(SQL_PROCEDURE, procedureTable, condition);

            return (sqlText, argumentsTable);
        }
    }
}
